package com.blb.dao.admin.impl;

import com.blb.dao.admin.ProductDao;
import com.blb.pojo.Product;
import com.blb.utils.JDBCUtils;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ProductDaoImpl implements ProductDao {

    @Override
    public Product selectByName(String name) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        Product product = JDBCUtils.getOne(connection, Product.class, "select * from t_product where Pname = ?", name);
        connection.close();
        return product;
    }

    /**
     * product和Brand表联查——product_type_id
     * @return
     * @throws Exception
     */
    public List<Product> selectProductAndBrand(String id) throws Exception {
        List<Product> list;
        Connection connection = JDBCUtils.getConnection();
        String sql = "select tp.*,tb.product_type_id from t_product tp left join t_brand tb on tb.id = tp.brand_id ";
        if (id != "当前不需要id"){
            sql += " where tp.id = ? ";
            list = JDBCUtils.getList(connection, Product.class, sql,Integer.parseInt(id));
        }else {
            list = JDBCUtils.getList(connection, Product.class, sql);
        }
        connection.close();
        return list;
    }

    @Override
    public List<Product> selectPageByName(int page, int size, String name) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select p.*, b.brand_name from t_product p left join t_brand b on p.brand_id = b.id ";
        ArrayList<Object> params = new ArrayList<>();

        if(name!=null && !name.isEmpty()){
            sql+=" where p.name like ? ";
            params.add("%"+name+"%");
        }
        sql+=" limit ?,?";
        params.add((page-1)*size);
        params.add(size);

        List<Product> list = JDBCUtils.getList(connection, Product.class, sql, params.toArray());
        connection.close();
        return list;
    }

    @Override
    public int count(String name) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(id) count from t_product ";
        ArrayList<Object> params = new ArrayList<>();

        if(name!=null && !name.isEmpty()){
            sql+=" where Pname like ?";
            params.add("%"+name+"%");
        }

        ResultSet resultSet = JDBCUtils.prepareStatement(connection, sql, params.toArray()).executeQuery();
        resultSet.next();
        int count = resultSet.getInt("count");
        connection.close();
        return count;
    }

    @Override
    public void insert(Product product) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        JDBCUtils.prepareStatement(connection, "insert into t_product(Pname, `desc`, img, details_img, price, brand_id, create_time, status) values(?,?,?,?,?,?,?,?)",
                product.getName(), product.getDesc(), product.getImg(), product.getDetailsImg(),
                product.getPrice(), product.getBrandId(), product.getCreateTime(), product.getStatus()).executeUpdate();
        connection.close();
    }

    @Override
    public void deleteById(int id) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        JDBCUtils.prepareStatement(connection, "delete from t_product where id = ?", id).executeUpdate();
        connection.close();
    }

    @Override
    public void batchDelete(String[] ids) throws SQLException, ClassNotFoundException {
        String sql = "delete from t_product where id in (";

        for (int i = 0; i < ids.length; i++) {
            sql+="?";
            if(i!=ids.length-1){
                sql+=",";
            }
        }

        sql+=")";
        Connection connection = JDBCUtils.getConnection();
        JDBCUtils.prepareStatement(connection, sql, ids).executeUpdate();
        connection.close();
    }

    @Override
    public Product selectById(int id) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        Product product = JDBCUtils.getOne(connection, Product.class, "select p.*, b.brand_name, b.product_type_id from t_product p left join t_brand b on p.brand_id = b.id where p.id = ?", id);
        connection.close();
        return product;
    }

    @Override
    public void update(Product product) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        JDBCUtils.prepareStatement(connection, "update t_product set Pname=?, `desc`=?, img=?, details_img=?, price=?, brand_id=? where id = ?",
                product.getName(), product.getDesc(), product.getImg(), product.getDetailsImg(),
                product.getPrice(), product.getBrandId(), product.getId()).executeUpdate();
        connection.close();
    }

    @Override
    public void updateStatusById(int id, int status) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        JDBCUtils.prepareStatement(connection, "update t_product set status=? where id = ?", status, id).executeUpdate();
        connection.close();
    }
}
